/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import BE.Artist;
import BE.Category;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author BentH
 */
public class CategoryDBManager
{

    private static CategoryDBManager instance = null;
    private final DBConnectionManager conMgr;

    private CategoryDBManager() throws IOException
    {
        conMgr = DBConnectionManager.getInstance();
    }

    public static CategoryDBManager getInstance() throws IOException
    {
        if (instance == null)
        {
            instance = new CategoryDBManager();
        }
        return instance;
    }

       public Category insert(Category c) throws SQLException
    {
        try (Connection con = conMgr.getConnection())
        {
            String sql = "INSERT INTO Category(Name) VALUES ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, c.getName());
            int affectedRows = ps.executeUpdate();
            if (affectedRows == 0)
            {
                throw new SQLException("Unable to add Category");
            }
            ResultSet keySet = ps.getGeneratedKeys();
            keySet.next();
            int artistId = keySet.getInt(1);
            return new Category(artistId, c.getName());
        }
    }

    public List<Category> getAll() throws IOException, SQLException
    {
        List<Category> Categories = new ArrayList<>();
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Category";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next())
            {
                Categories.add(getOneCategory(rs));
            }
            return Categories;
        }

    }

    public Category getById(int id) throws SQLException, IOException
    {
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Category WHERE id = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                return getOneCategory(rs);
            }
        }
        return null;
    }

    public Category getByName(String name) throws SQLException, IOException
    {
        try (Connection con = conMgr.getConnection())
        {
            String sql = "SELECT * FROM Category WHERE name Like ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                return getOneCategory(rs);
            }
        }
        return null;
    }
    
    private Category getOneCategory(ResultSet rs) throws SQLException, IOException
    {
        int id = rs.getInt("Id");
        String name = rs.getString("Name");
        Category category = new Category(id, name);
        category.setSongs(SongDBManager.getInstance().getSongsByCategoryId(id));
        return category;
    }


}
